package cn.hxzy.course.mapper;

import cn.hxzy.common.domain.ClassMenu;
import cn.hxzy.common.domain.Course;
import cn.hxzy.common.domain.vo.CourseVoList;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

/**
 * @Author LiDong
 * @Description
 * @create 2024/7/30 10:46
 */
public interface CourseMapper extends BaseMapper<Course> {
    //查当前登录用户班级的课程
    @Select(" SELECT *  FROM sys_course  WHERE course_id IN(\n" +
            " SELECT course_id FROM sys_class_menu WHERE class_id=#{classId})  ")
    List<Course> selectByClassId(Integer classId);


    //查所有包含directionId的课程
    @Select("SELECT * FROM sys_course WHERE course_id IN(\n" +
            "SELECT course_id FROM sys_class_menu WHERE class_id=#{classId}) AND \n" +
            " FIND_IN_SET(#{directionId}, direction_id) > 0")
    List<Course> selectListByDid(int directionId,Integer classId);

    //查所有包含professionalId的课程
    @Select("   SELECT *  FROM sys_course  WHERE course_id IN(\n" +
            " SELECT course_id FROM sys_class_menu WHERE class_id=#{classId}) AND professional_id=#{professionalId}")
    List<Course> selectListByPid(int professionalId,Integer classId);

    //专业编号和方向编号
    @Select(" SELECT *  FROM sys_course  WHERE course_id IN(\n" +
            " SELECT course_id FROM sys_class_menu WHERE class_id=#{classId})\n" +
            " AND professional_id = #{professionalId}\n" +
            " AND FIND_IN_SET(#{directionId}, direction_id) > 0;")
    List<Course> selectListByPidAndDid(Integer professionalId, Integer directionId,Integer classId);
    //模糊查询
    @Select(" SELECT *  FROM sys_course  WHERE course_id IN(\n" +
            " SELECT course_id FROM sys_class_menu WHERE class_id=#{classId}) \n" +
            " AND course_name LIKE CONCAT('%',#{courseName},'%')")
    List<Course> selectListByName(String courseName,Integer classId);
    @Select(" SELECT *  FROM sys_course  WHERE course_id IN(\n" +
            " SELECT course_id FROM sys_class_menu WHERE class_id=#{classId}) AND\n" +
            " FIND_IN_SET(#{directionId}, direction_id) > 0\n" +
            " AND course_name LIKE CONCAT('%',#{courseName},'%')")
    List<Course> selectListByNameAndDid(String courseName, Integer directionId, Integer classId);
    //类型和课程名字模糊查询
    @Select(" SELECT *  FROM sys_course  WHERE course_id IN(\n" +
            "SELECT course_id FROM sys_class_menu WHERE class_id=#{classId})\n" +
            "AND professional_id=#{professionalId} AND course_name LIKE CONCAT('%',#{courseName},'%')\n")
    List<Course> selectListByNameAndPid(String courseName,Integer professionalId,Integer classId);
    //根据类型编号和方向编号和课程名字模糊查询
    @Select("SELECT *  FROM sys_course  WHERE course_id IN(\n" +
            " SELECT course_id FROM sys_class_menu WHERE class_id=#{classId}) " +
            " AND professional_id=#{professionalId}" +
            " AND FIND_IN_SET(#{directionId}, direction_id) > 0 " +
            "AND course_name LIKE CONCAT('%',#{courseName},'%')")
    List<Course> selectListByNameAndPidAndDid(String courseName,Integer professionalId,Integer directionId,Integer classId);
    //查所有包含directionId的课程  老师版本
    @Select("SELECT * FROM sys_course WHERE school_id=#{schoolId} AND  FIND_IN_SET(#{directionId}, direction_id) > 0")
    List<Course> selectListBySid(int directionId,Integer schoolId);
    //查所有包含professionalId的课程  老师版
    @Select("SELECT *  FROM sys_course  WHERE school_id =#{schoolId} AND professional_id=#{professionalId}")
    List<Course> selectListByPidSid(int professionalId,Integer schoolId);
    //专业编号和方向编号  老师版
    @Select("  SELECT *  FROM sys_course  WHERE school_id=#{schoolId}\n" +
            "             AND professional_id = #{professionalId} \n" +
            "            AND FIND_IN_SET(#{directionId}, direction_id) > 0;")
    List<Course> selectListByPidAndDidSid(Integer professionalId, Integer directionId,Integer schoolId);
    //模糊查询 老师版
    @Select("SELECT *  FROM sys_course  WHERE school_id=#{schoolId} AND course_name LIKE CONCAT('%',#{courseName},'%')")
    List<Course> selectListByNameSid(String courseName,Integer schoolId);
    //老师

    @Select(" SELECT *  FROM sys_course\n" +
            "  WHERE school_id=#{schoolId} \n" +
            "   AND FIND_IN_SET(#{directionId}, direction_id) > 0\n" +
            "    AND course_name LIKE CONCAT('%',#{courseName},'%')")
    List<Course> selectListByNameAndDidSid(String courseName, Integer directionId, Integer schoolId);
    //类型和课程名字模糊查询  老师版
    @Select("   SELECT *  FROM sys_course  WHERE school_id=#{schoolId}\n" +
            "   AND professional_id=#{professionalId} AND course_name LIKE CONCAT('%',#{courseName},'%')")
    List<Course> selectListByNameAndPidSid(String courseName,Integer professionalId,Integer schoolId);
    //根据类型编号和方向编号和课程名字模糊查询 老师版
    @Select("   SELECT *  FROM sys_course  WHERE school_id=#{schoolId}\n" +
            "            AND professional_id=#{professionalId} \n" +
            "             AND FIND_IN_SET(#{directionId}, direction_id) > 0 \n" +
            "            AND course_name LIKE CONCAT('%',#{courseName},'%')")
    List<Course> selectListByNameAndPidAndDidSid(String courseName,Integer professionalId,Integer directionId,Integer schoolId);





    @Select("SELECT t1.*,t2.`direction_name`,t3.`nick_name`\n" +
            "FROM \n" +
            "sys_course t1\n" +
            "INNER JOIN `sys_direction` t2\n" +
            "ON t1.`direction_id`=t2.`direction_id`\n" +
            "INNER JOIN sys_user t3\n" +
            "ON t1.`create_user`=t3.`user_id`\n "+
            "where t1.create_user=#{courseId} and t1.school_id=#{schoolId} \n"+
            " ORDER BY   \n" +
            "    t1.create_time DESC;")
    List<CourseVoList> getList(Integer courseId,Integer schoolId);
    @Select("SELECT t1.*,t2.`direction_name`,t3.`nick_name`\n" +
            "FROM \n" +
            "sys_course t1\n" +
            "INNER JOIN `sys_direction` t2\n" +
            "ON t1.`direction_id`=t2.`direction_id`\n" +
            "INNER JOIN sys_user t3\n" +
            "ON t1.`create_user`=t3.`user_id`\n" +
            "where t3.user_id=#{userId}")
    List<CourseVoList> getUserIdList(Integer userId);


    @Select("SELECT t1.*,t2.`direction_name`,t3.`nick_name`\n" +
            "FROM \n" +
            "sys_course t1\n" +
            "INNER JOIN `sys_direction` t2\n" +
            "ON t1.`direction_id`=t2.`direction_id`\n" +
            "INNER JOIN sys_user t3\n" +
            "ON t1.`create_user`=t3.`user_id`\n" +
            "WHERE t1.course_name LIKE CONCAT('%',#{courseName},'%')\n"+
            " ORDER BY   \n" +
            "    t1.create_time DESC;")
    List<CourseVoList> selectCourceListByName(String courseName);
    @Select("SELECT\n" +
            "t1.*,t2.`direction_name`,t3.`user_name`\n" +
            "FROM \n" +
            "sys_course t1\n" +
            "INNER JOIN `sys_direction` t2\n" +
            "ON t1.`direction_id`=t2.`direction_id`\n" +
            "INNER JOIN sys_user t3\n" +
            "ON t1.`create_user`=t3.`user_id`\n" +
            "WHERE FIND_IN_SET(#{directionId}, t2.direction_id) > 0")
    List<CourseVoList> selectListDirectionId(String directionId);

    @Select("select * from sys_course where course_name=#{courseName} and deleted=0")
    Course getCourseByName(String courseName);

    @Select("SELECT\n" +
            "t1.*,t2.`direction_name`,t3.`user_name`\n" +
            "FROM \n" +
            "sys_course t1\n" +
            "INNER JOIN `sys_direction` t2\n" +
            "ON t1.`direction_id`=t2.`direction_id`\n" +
            "INNER JOIN sys_user t3\n" +
            "ON t1.`create_user`=t3.`user_id`\n" +
            "WHERE FIND_IN_SET(#{directionId}, direction_id) > 0 AND\n" +
            " course_name LIKE CONCAT('%',#{courseName},'%')")

    List<CourseVoList> getFilteredCoursesByCourseNameAndDirection(String courseName, String directionId);

    @Select("select course_name from  sys_course where create_user=#{userId}")
    List<String> getCourseLists(int userId);


    //查询课程是否有班级关联
    @Select("SELECT count(1) FROM `sys_class_menu` WHERE course_id=#{courseId}")
    Integer selectClassMenuByCourseId(Integer courseId);

}
